package spt.slides;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Logger;

import spt.exceptions.PersistanceException;
import spt.exceptions.ValidationException;
import spt.utils.DBAdapter;

public class SlideDAO {
	@SuppressWarnings("unused")
	private final Logger log = Logger.getLogger(SlideDAO.class.getName());
	
	public Slide get(int id) throws PersistanceException{
		Slide slide = null;
		Connection connection = DBAdapter.getConnection();
		PreparedStatement query = null;
		ResultSet rs = null;
		try {
			query = connection.prepareStatement("select id, content, pub_start, pub_stop, display_time, always_pub from slides where id = ?");
			query.setInt(1, id);
			rs = query.executeQuery();
			
			if(rs.next()){
				slide = new Slide();
				slide.setId(rs.getInt("id"));
				slide.setContent(rs.getString("content"));
				slide.setPublishStart(rs.getTimestamp("pub_start"));
				slide.setPublishStop(rs.getTimestamp("pub_stop"));
				slide.setDisplayTime(rs.getInt("display_time"));
				slide.setAlwaysPublished(rs.getBoolean("always_pub"));
				return slide;
			}
		} catch (SQLException e) {
			throw new PersistanceException("Connection error.");
		} finally{
			DBAdapter.closeConnection(connection, query, rs);
		}
		
		return null;
	}
	
	public List<Integer> getIds() throws PersistanceException{
		Connection connection = DBAdapter.getConnection();
		PreparedStatement query = null;
		ResultSet rs = null;
		List<Integer> ids = new ArrayList<Integer>();
		try {
			query = connection.prepareStatement("select id from slides");
			rs = query.executeQuery();
			
			while(rs.next())
				ids.add(rs.getInt("id"));
				
		} catch (SQLException e) {
			throw new PersistanceException("Connection error.");
		} finally{
			DBAdapter.closeConnection(connection, query, rs);
		}
		
		return ids;
	}
	
	public int save(Slide slide) throws PersistanceException, ValidationException{
		Connection connection = DBAdapter.getConnection();
		PreparedStatement query = null;
		int newId = 0;
		
		try {
			connection.setAutoCommit(false);
			
			slide.validate();
			
			newId = getNextId(connection);
			
			query = connection.prepareStatement("insert into slides values (?, ?, ?, ?, ?, ?)");
			query.setInt(1, newId);
			query.setString(2, slide.getContent());
			query.setTimestamp(3, slide.getPublishStart());
			query.setTimestamp(4, slide.getPublishStop());
			query.setInt(5, slide.getDisplayTime());
			query.setBoolean(6, slide.isAlwaysPublished());
			
			query.executeUpdate();
			
			connection.commit();
		} catch (SQLException e) {
			throw new PersistanceException("Connection error.");
		} catch (ValidationException e) {
			throw e;
		} finally {
			DBAdapter.closeConnection(connection, query);
		}
		
		return newId;
	}
	
	public void update(Slide slide) throws PersistanceException, ValidationException {
		Connection connection = DBAdapter.getConnection();
		PreparedStatement query = null;
	
		try {
			connection.setAutoCommit(false);
			
			slide.validate();
			
			query = connection.prepareStatement("update slides set (content, pub_start, pub_stop, display_time, always_pub) = (?, ?, ?, ?, ?) where id = ?");
			query.setString(1, slide.getContent());
			query.setTimestamp(2, slide.getPublishStart());
			query.setTimestamp(3, slide.getPublishStop());
			query.setInt(4, slide.getDisplayTime());
			query.setBoolean(5, slide.isAlwaysPublished());
			query.setInt(6, slide.getId());
			
			query.executeUpdate();
			
			connection.commit();
		} catch (SQLException e) {
			throw new PersistanceException("Connection error.");
		} catch (ValidationException e) {
			throw e;
		} finally {
			DBAdapter.closeConnection(connection, query);
		}
	}
	
	public void delete(int id) throws PersistanceException{
		Connection connection = DBAdapter.getConnection();
		PreparedStatement query = null;
		try {
			query = connection.prepareStatement("delete from slides where id = ?");
			query.setInt(1, id);
			query.executeUpdate();
		} catch (SQLException e) {
			throw new PersistanceException("Connection error.");
		} finally{
			DBAdapter.closeConnection(connection, query);
		}
	}
	
	public List<Slide> getCurrentSlides() throws PersistanceException{
		Timestamp currentDate = new Timestamp(new Date().getTime());
		List<Slide> slides = new ArrayList<Slide>();
		Connection connection = DBAdapter.getConnection();
		PreparedStatement query = null;
		ResultSet rs = null;
		try {
			query = connection.prepareStatement(
					"select id, content, pub_start, pub_stop, display_time, always_pub from slides where ((pub_start < ? and pub_stop > ?) or always_pub is true) and id != 1 order by id");
			
			query.setTimestamp(1, currentDate);
			query.setTimestamp(2, currentDate);
			
			rs = query.executeQuery();
			
			while(rs.next()){
				Slide slide = new Slide();
				slide.setId(rs.getInt("id"));
				slide.setContent(rs.getString("content"));
				slide.setPublishStart(rs.getTimestamp("pub_start"));
				slide.setPublishStop(rs.getTimestamp("pub_stop"));
				slide.setDisplayTime(rs.getInt("display_time"));
				slide.setAlwaysPublished(rs.getBoolean("always_pub"));
				slides.add(slide);
			}
		} catch (SQLException e) {
			throw new PersistanceException("Connection error.");
		} catch (NullPointerException e){
			throw new PersistanceException("Connection error.");
		} finally {
			DBAdapter.closeConnection(connection, query, rs);
		}
		
		return slides;
	}
	
	/**
	 * Gets next value from sequence as id for new record. 
	 * @param connection Connection with opened transaction.
	 * @return Next value from sequence <b>slide_seq</b>
	 * @throws SQLException Throw when some problem with database occurs. 
	 */
	private int getNextId(Connection connection) throws SQLException{
		PreparedStatement query = connection.prepareStatement("select nextval('slide_seq') as id");
		ResultSet rs = query.executeQuery();
		int id = 0;
		if(rs.next()){
			id = rs.getInt("id");
		} 
		
		query.close();
		rs.close();
		
		if(id != 0)
			return id;
		else
			throw new SQLException("Could not get id from sequence.");
	}
}
